﻿Imports System.Data.OleDb
Public Class QuanLyThuDAO
    Shared conn_str As String = My.Settings.ConnectionString
    Shared Function GetAll(Optional MaUser As Integer = 0) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select MaPhieuThu,ThuNhap.Ten loai_thu_nhap,NganHang.ten TaiKhoan,lap.fullname ten_nguoi_lap,thu.fullname ten_nguoi_thu,Ngay,SoTien,ChiTiet,PhieuThu.MaNganHang,PhieuThu.MaUserThu,PhieuThu.MaUserLap,PhieuThu.MaThuNhap,MONTH(ngay) thangthu,YEAR(ngay) namthu from PhieuThu " & _
            "left join ThuNhap on ThuNhap.MaThuNhap = PhieuThu.MaThuNhap " & _
            "left join NganHang on NganHang.MaNganHang = PhieuThu.MaNganHang " & _
            "inner join Users lap on lap.MaUser = PhieuThu.MaUserLap " & _
            "inner join Users thu on thu.MaUser = PhieuThu.MaUserThu"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            If MaUser = 0 Then
                query = query & " where MaUser = ?"
                adapter.SelectCommand.Parameters.Add("@MaUser", OleDbType.Integer, 11).Value = MaUser
            End If
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function GetAllByMa(ByVal MaPhieuThu As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select PhieuThu.*,users.fullname from PhieuThu " & _
            "inner join users on users.MaUser = PhieuThu.MaUserThu " & _
            "where MaPhieuThu = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.Add("@MaPhieuThu", OleDbType.Integer, 11).Value = MaPhieuThu
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function GetReportData(ByVal MaUser As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select sum(SoTien) as sotien,MONTH(ngay) thangthu,YEAR(ngay) namthu from PhieuThu " & _
        "where(MaUserThu = ?) " & _
        "group by YEAR(ngay),MONTH(ngay)"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.Add("@MaUser", OleDbType.Integer, 11).Value = MaUser
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Public Function Write(ByVal table As DataTable) As Integer
        Dim Kq As Integer = 0
        Dim query As String = "Select * From PhieuThu"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            Dim builder As New OleDbCommandBuilder(adapter)            
            Kq = adapter.Update(table)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return Kq
    End Function
    Shared Function TrungBinh(ByVal MaUser As Integer) As Double
        Dim tb As Double
        Dim query As String = "select avg(sotien) as sotien from " & _
        "(select sum(sotien) as sotien,MaUserThu from PhieuThu where MaUserThu = ? group by MaUserThu,MONTH(Ngay)) as a " & _
        "where MaUserThu = ?"
        Try
            Using conn As New OleDbConnection(conn_str)
                Dim cmd As New OleDbCommand(query, conn)
                cmd.Parameters.AddWithValue("@MaUserThu", MaUser)
                cmd.Parameters.AddWithValue("@MaUserThu", MaUser)
                conn.Open()
                Double.TryParse(cmd.ExecuteScalar().ToString(), tb)
                conn.Close()
            End Using
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return tb
    End Function
    Shared Function TongTien(ByVal MaUser As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select sum(sotien) as sotien, year(ngay) as nam from PhieuThu " & _
        "where MaUserThu = ? GROUP BY year(ngay)"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.Add("@MaUserThu", OleDbType.Integer, 11).Value = MaUser
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
End Class
